查看原文
其他

还傻傻分不清MySQL回表查询与索引覆盖?

张张 架构精进之路
2024-08-31

hello,大家好,我是张张,「架构精进之路」公号作者。


最近的工作中,遇到一个查询里用到主键索引与二级索引并存的问题情况,那对于这种情况,索引是如何高效执行的,是否会产生回表查询呢?
等等,首先解释一下,什么是回表?
回表定义:先索引扫描,再通过ID去取索引中未能提供的数据,即为回表。
即先定位主键值,再定位行记录。


1、两类索引






为了更好地阐释这个问题,我们还是从索引来介绍吧。
InnoDB 索引分为两大类,一类是聚集索引(Clustered Index),一类是非聚集索引(Secondary Index)

1.1 聚集索引(聚簇索引)

InnoDB聚集索引的叶子节点存储行记录,因此InnoDB必须要有且只有一个聚集索引。
  • 如果表定义了PK(Primary Key,主键),那么PK就是聚集索引。
  • 如果表没有定义PK,则第一个NOT NULL UNIQUE的列就是聚集索引。
  • 否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。
这种机制使得基于PK的查询速度非常快,因为直接定位的行记录。

1.2 非聚集索引(普通索引、非聚簇索引、二级索引)

普通索引也叫二级索引,除聚簇索引外的索引,即非聚簇索引。
InnoDB的普通索引叶子节点存储的是主键(聚簇索引)的值,而MyISAM的普通索引存储的是记录指针。

Q:为什么非主键索引结构叶子结点存储的是主键值?
A:减少了出现行移动或者数据页分裂时二级索引的维护工作(当数据需要更新的时候,二级索引不需要修改,只需要修改聚簇索引,一个表只能有一个聚簇索引,其他的都是二级索引,这样只需要修改聚簇索引就可以了,不需要重新构建二级索引)
在使用非聚集索引时,为了取到具体数据,则需要通过PK回到聚集索引里去查询数据。这就叫回表查询,扫描了2次索引树,所以效率相对较低。

2、应用示例






一例胜千言,show me you code!

2.1 建表操作

mysql> create table user( -> id int(10) auto_increment, -> name varchar(30), -> sex tinyint(4), -> type varchar(8), -> primary key (id), -> index idx_name (name) -> )engine=innodb charset=utf8mb4;
id 字段是聚簇索引,name 字段是普通索引(二级索引)

2.2 填充数据

mysql> select * from user;+----+--------+------+------+| id | name | sex | type |+----+--------+------+------+| 1 | sj | m | A || 3 | zs  |  m  |  A  || 5 | ls | m | A || 9 | ww  |  f  |  B  |+----+-----+-----+-----+

2.3 索引结构

  • 聚簇索引(ClusteredIndex)
id 是主键,所以是聚簇索引,其叶子节点存储的是对应行记录的数据


  • 普通索引(secondaryIndex)
name 是普通索引(二级索引),非聚簇索引,其叶子节点存储的是聚簇索引的的值

2.4 查找过程

  • 普通索引查找过程
如果查询条件为主键(聚簇索引),则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。
select * from user where name = 'lisi';
普通索引因为无法直接定位行记录,其查询过程在通常情况下是需要扫描两遍索引树的。
实际执行过程:

路径需要扫描两遍索引树,第一遍先通过普通索引定位到主键值id=5,然后第二遍再通过聚集索引定位到具体行记录。
这就是所谓的回表查询,即先定位主键值,再根据主键值定位行记录,性能相对于只扫描一遍聚集索引树的性能要低一些。

3、索引覆盖






索引覆盖是一种避免回表查询的优化策略。
只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。

3.1 如何实现覆盖索引

将要查询的数据作为索引列建立普通索引(可以是单列索引,也可以一个索引语句定义所有要查询的列,即联合索引),这样的话就可以直接返回索引中的的数据,不需要再通过聚集索引去定位行记录,避免了回表的情况发生。
explain select id, name from user where name = 'lisi';
explain分析:因为name是普通索引,使用到了name索引,通过一次扫描B+树即可查询到相应的结果,这样就实现了覆盖索引



往期热文推荐:


关注公众号,免费领学习资料

如果您觉得还不错,欢迎关注和转发~     

继续滑动看下一个
架构精进之路
向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存